﻿
-- ================================================
-- Remarks: 
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'Ivy_GetUserAuthorizationByContentAndActivity')
	BEGIN
		DROP  Procedure  Ivy_GetUserAuthorizationByContentAndActivity
	END

GO

CREATE PROCEDURE [Ivy_GetUserAuthorizationByContentAndActivity]
-- =============================================
-- Author:		<Mrinmoy Das>
-- Create date: <02-05-2015>
-- Description:	<Ivy_GetUserAuthorizationByContentAndActivity>
-- =============================================
-- Add the parameters for the stored procedure here
(
		@UserKeyAsString varchar(50),
		@ContentKey int,
		@ActivityTypeCode varchar(50),
		@ContentTypeCode varchar(50) = Null
)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT OFF;

    -- Insert statements for procedure here
	IF @ActivityTypeCode <> 'CRT'
		BEGIN
			SELECT		
						[ContentMetaDataMaster].[ContentKey], 
						[ContentMetaDataMaster].[ContentCode], 
						[ContentMetaDataMaster].[ContentTypeKey], 
						[ContentTypeMaster].[ContentTypeCode], 
						[ContentTypeMaster].[ContentTypeName], 
						[ContentMetaDataMaster].[Sys_CreatedByID], 
						[User].[UserLoginID], 
						[Sys_RoleMaster].[RoleCode], 
						[Sys_RoleMaster].[RoleName], 
						[Sys_ActivityMaster].[ActivityCode], 
						[Sys_ActivityMaster].[ActivityName], 
						[Sys_ActivityTypeMaster].[ActivityTypeCode], 
						[Sys_ActivityTypeMaster].[ActivityTypeName] 
			FROM         
						[ContentTypeMaster] RIGHT OUTER JOIN [ContentMetaDataMaster] 
			ON			[ContentTypeMaster].[ContentTypeKey] = [ContentMetaDataMaster].[ContentTypeKey] LEFT OUTER JOIN 
						[Sys_ActivityTypeMaster] RIGHT OUTER JOIN [Sys_ActivityMaster] 
			ON			[Sys_ActivityTypeMaster].[ActivityTypeKey] = [Sys_ActivityMaster].[ActivityTypeKey] RIGHT OUTER JOIN [Sys_RoleActivity] 
			ON			[Sys_ActivityMaster].[ActivityKey] = [Sys_RoleActivity].[RoleActivityKey] RIGHT OUTER JOIN [Sys_RoleMaster] 
			ON			[Sys_RoleActivity].[RolerKey] = [Sys_RoleMaster].[RoleKey] RIGHT OUTER JOIN [UserRole] 
			ON			[Sys_RoleMaster].[RoleKey] = [UserRole].[UserRoleKey] RIGHT OUTER JOIN [User] 
			ON			[UserRole].[UserKey] = [User].[UserKey] 
			ON			[ContentMetaDataMaster].[Sys_CreatedByID] = [User].[UserKey] 
			WHERE     
						(
							([ContentMetaDataMaster].[ContentKey] = @ContentKey) AND 
							([ContentMetaDataMaster].[Sys_CreatedByID] = @UserKeyAsString) AND 
							([Sys_ActivityTypeMaster].[ActivityTypeCode] = @ActivityTypeCode) AND 
							([Sys_ActivityMaster].[ActivityCode] LIKE '%' + [ContentTypeMaster].[ContentTypeCode] + '%')
						)
		END
	ELSE
		BEGIN
			SELECT     [User].[UserKey], 
					   [User].[UserLoginID], 
					   [Sys_RoleMaster].[RoleName], 
					   [Sys_RoleMaster].[RoleCode], 
					   [Sys_ActivityMaster].[ActivityCode], 
					   [Sys_ActivityMaster].[ActivityName], 
					   [Sys_ActivityTypeMaster].[ActivityTypeCode], 
					   [Sys_ActivityTypeMaster].[ActivityTypeName] 
			FROM         
					   [Sys_ActivityMaster] LEFT OUTER JOIN [Sys_ActivityTypeMaster] 
			ON		   [Sys_ActivityMaster].[ActivityTypeKey] = [Sys_ActivityTypeMaster].[ActivityTypeKey] RIGHT OUTER JOIN [Sys_RoleActivity] 
			ON		   [Sys_ActivityMaster].[ActivityKey] = [Sys_RoleActivity].[RoleActivityKey] RIGHT OUTER JOIN [Sys_RoleMaster] 
			ON		   [Sys_RoleActivity].[RolerKey] = [Sys_RoleMaster].[RoleKey] RIGHT OUTER JOIN [UserRole] 
			ON		   [Sys_RoleMaster].[RoleKey] = [UserRole].[UserRoleKey] RIGHT OUTER JOIN [User] 
			ON		   [UserRole].[UserKey] = [User].[UserKey] 
			WHERE     
					  (
							([User].[UserKey] = CONVERT(INT, @UserKeyAsString)) AND  
							([Sys_ActivityTypeMaster].[ActivityTypeCode] = @ActivityTypeCode) AND 
							([Sys_ActivityMaster].[ActivityCode] LIKE '%' + @ContentTypeCode + '%')
					  )
		END
END
GO